package com.nway.servlet;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import test.User;

import com.nway.commons.dbutils.BatchCallback;
import com.nway.commons.dbutils.CallableRunner;
import com.nway.commons.dbutils.ResultSetHandler;
import com.nway.commons.dbutils.SQLRunner;
import com.nway.commons.dbutils.handlers.BeanHandler;
import com.nway.commons.dbutils.handlers.BeanListHandler;
import com.nway.commons.dbutils.handlers.MapListHandler;
import com.nway.commons.servlet.BaseServlet;

public class TestServlet extends BaseServlet {

	private static final long serialVersionUID = -7835236693243304566L;

	private static final Logger logger = LoggerFactory.getLogger(TestServlet.class);

	public String dbQuery(HttpServletRequest req, HttpServletResponse resp) {

		try {
 
			ResultSetHandler<List<User>> u = new ResultSetHandler<List<User>>() {
				public List<User> handle(ResultSet rs) throws SQLException {
					List<User> users = new ArrayList<User>();

					if (rs.next()) {

						final User user = new User();
						user.setCdate(rs.getDate(28));
						user.setCdate2(rs.getDate(28));
						user.setCdate3(rs.getDate(28));
						user.setCdate4(rs.getDate(28));
						user.setCdate5(rs.getDate(28));
						user.setCdate6(rs.getDate(28));
						user.setCdate7(rs.getDate(28));
						user.setCdate8(rs.getDate(28));
						user.setCdate9(rs.getDate(28));
						user.setCdate10(rs.getDate(28));
						user.setCdate11(rs.getDate(28));
						user.setName(rs.getString(2));
						user.setName1(rs.getString(2));
						user.setName2(rs.getString(2));
						user.setName3(rs.getString(2));
						user.setName4(rs.getString(2));
						user.setName5(rs.getString(2));
						user.setName6(rs.getString(2));
						user.setName7(rs.getString(2));
						user.setName8(rs.getString(2));
						user.setName9(rs.getString(2));
						user.setName10(rs.getString(2));
						user.setName11(rs.getString(2));
						user.setName12(rs.getString(2));
						user.setName13(rs.getString(2));
						user.setName14(rs.getString(2));
						user.setName15(rs.getString(2));
						user.setName16(rs.getString(2));
						user.setName17(rs.getString(2));

						users.add(user);
					}

					return users;
				}
			};

			final User us = new User();

			SQLRunner.getInstance().execute(new BatchCallback<Long>() {

				@Override
				public Long doBatch(CallableRunner cr) throws SQLException {

					us.getCdate();

					Connection conn = cr.getConn();

					conn.setAutoCommit(false);

					return null;
				}
			});

			long begin = System.currentTimeMillis();
			List<User> users = SQLRunner.getInstance().query("SELECT * FROM t_user", null,// u);
					new BeanListHandler<User>(User.class));

			logger.info("Size = " + users.size() + " QueryRunner List time = " + (System.currentTimeMillis() - begin));

			begin = System.currentTimeMillis();

			User usr = SQLRunner.getInstance().query("SELECT * FROM t_user", null, new BeanHandler<User>(User.class));

			logger.info("QueryRunner Bean time = " + (System.currentTimeMillis() - begin));

			begin = System.currentTimeMillis();
			List<Map<String, Object>> musers = SQLRunner.getInstance().query("SELECT * FROM t_user", null,
					new MapListHandler());

			logger.info("Size = " + musers.size() + " QueryRunner Map time = " + (System.currentTimeMillis() - begin));

			Context context = new InitialContext();
			DataSource dataSource = (DataSource) context.lookup("java:/comp/env/jdbc/wiscomccs");

			begin = System.currentTimeMillis();
			Connection conn3 = dataSource.getConnection();
			PreparedStatement stmt2 = conn3.prepareStatement("SELECT * FROM t_user");

			ResultSet rs = stmt2.executeQuery();
			List<Map<String, Object>> data = new ArrayList<Map<String, Object>>();

			ResultSetMetaData rsmd = rs.getMetaData();

			int columnCount = rsmd.getColumnCount();

			String colName = "";

			while (rs.next()) {
				Map<String, Object> tmp = new HashMap<String, Object>();

				for (int i = 1; i <= columnCount; i++) {
					colName = rsmd.getColumnLabel(i);

					Object obj2 = rs.getObject(colName);

					if (obj2 == null) {
						obj2 = "";
					}

					tmp.put(colName, obj2);
				}

				data.add(tmp);
			}

			rs.close();
			stmt2.close();
			conn3.close();
			logger.info("Size = " + musers.size() + " JDBC List time = " + (System.currentTimeMillis() - begin));
			System.out.println();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return "/test.jsp";
	}

	/**
	 * 建表： create table t_user (id integer primary key,name varchar(255),name1
	 * varchar(255),name2 varchar(255),name3 varchar(255),name4
	 * varchar(255),name5 varchar(255),name6 varchar(255),name7
	 * varchar(255),name8 varchar(255),name9 varchar(255),name10
	 * varchar(255),name11 varchar(255),name12 varchar(255),name13
	 * varchar(255),name14 varchar(255),name15 varchar(255),name16
	 * varchar(255),name17 varchar(255),cdate date,cdate2 date,cdate3
	 * date,cdate4 date,cdate5 date,cdate6 date,cdate7 date,cdate8 date,cdate9
	 * date,cdate10 date,cdate11 date);
	 * 
	 * @param req
	 * @param resp
	 * @return
	 */
	public String dbInsert(HttpServletRequest req, HttpServletResponse resp) {

		try {

			SQLRunner run = SQLRunner.getInstance();

			long runnerBegin = System.currentTimeMillis();
			for (int i = 1; i <= 21; i++) {
				Object[] obj = new Object[] { runnerBegin + i, "abc", "abc", "abc", "abc", "abc", "abc", "abc", "abc",
						"abc", "abc", "abc", "abc", "abc", "abc", "abc", "abc", "abc", "abc", new Date(), new Date(),
						new Date(), new Date(), new Date(), new Date(), new Date(), new Date(), new Date(), new Date(),
						new Date() };
				run.update("INSERT INTO t_user  VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
						obj);

			}
			System.out.println("QueryRunner Insert Time = " + (System.currentTimeMillis() - runnerBegin));

			Context context = new InitialContext();
			DataSource dataSource = (DataSource) context.lookup("java:/comp/env/jdbc/wiscomccs");

			long jdbcBegin = System.currentTimeMillis();

			Connection conn = dataSource.getConnection();

			conn.setAutoCommit(false);
			PreparedStatement prpstmt = conn.prepareStatement("INSERT INTO USER (ID,NAME) VALUES (?,?)");

			for (int i = 1; i <= 21; i++) {
				prpstmt.setLong(1, jdbcBegin + i);
				prpstmt.setString(2, "abc");
				prpstmt.addBatch();
			}
			prpstmt.executeBatch();
			conn.commit();
			prpstmt.close();
			conn.close();

			System.out.println("JDBC Insert Time = " + (System.currentTimeMillis() - jdbcBegin));

		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

}
